SnowPro Advanced Administrator ADA-C01 v1.0
Question 1
An Administrator has a warehouse which is intended to have a credit quota set for 3000 for each calendar year. The Administrator needs to create a resource monitor that will perform the following tasks:
1. At 80% usage notify the account Administrators.
2. At 100% usage suspend the warehouse and notify the account Administrators.
3. At 120% stop all running executions, suspend the warehouse, and notify the account Administrators.
Which SQL command will meet these requirements?
-
A.
-
B.
-
C.
-
D.
Answer : B
Question 2
Which tasks can be performed by the ORGADMIN role? (Choose three.)
- A. Create one or more accounts in the organization.
- B. View a list of all regions enabled for the organization.
- C. Create secure views on application tables within the organization.
- D. View usage information for all accounts in the organization.
- E. Perform zero-copy cloning on account data.
- F. Create a reader account to share data with another organization.
Answer : ABD
Question 3
In which scenario will use of an external table simplify a data pipeline?
- A. When accessing a Snowflake table from a relational database
- B. When accessing a Snowflake table from an external database within the same region
- C. When continuously writing data from a Snowflake table to external storage
- D. When accessing a Snowflake table that references data files located in cloud storage
Answer : D
Question 4
Which commands can be performed by a user with the ORGADMIN role but not the ACCOUNTADMIN role? (Choose two.)
- A. SHOW REGIONS;
- B. SHOW USERS;
- C. SHOW ORGANIZATION ACCOUNTS;
- D. GRANT ROLE ORGADMIN TO USER <username>;
-
E.
Answer : AC
Question 5
A resource monitor named MONTHLY_FINANCE_LIMIT has been created and applied to two virtual warehouses (fin_wh1 and fin_wh2) using the following SQL:
On December 23rd, the combined total of credits consumed by fin_wh1 and fin_wh2, including cloud services, reaches 800 credits and both warehouses are suspended.
Which statements should the ACCOUNTADMIN execute to allow both warehouses to be resumed? (Choose two.)
-
A.
alter warehouse fin_wh1 resume:
alter warehouse fin_wh2 resume; -
B.
alter warehouse fin_wh1 unset resource monitor MONTHLY_FINANCE_LIMIT;
alter warehouse fin_wh2 unset resource monitor MONTHLY_FINANCE_LIMIT; - C. alter resource monitor MONTHLY_FINANCE_LIMIT set credit_quota = 1500;
- D. alter resource monitor MONTHLY_FINANCE_LIMIT reset;
-
E.
alter warehouse fin_wh1 unset resource_monitor;
alter warehouse fin_wh2 unset resource_monitor;
Answer : CD
Question 6
What are benefits of creating and maintaining resource monitors in Snowflake? (Choose three.)
- A. The cost of running a resource monitor is only 10% of a credit, per day of operation.
- B. Multiple resource monitors can be applied to a single virtual warehouse.
- C. Resource monitors add no additional load to virtual warehouse compute.
- D. Multiple triggers can be configured across various virtual warehouse thresholds.
- E. Resource monitor governance is tightly controlled and monitors can only be created by the ACCOUNTADMIN role or users with the CREATE MONITOR privilege.
- F. Resource monitors can be applied to more than one virtual warehouse.
Answer : BDF
Question 7
What are the MINIMUM grants required on the database, schema, and table for a stream to be properly created and managed?
-
A.
Database: Usage
Schema: Usage
Table: Select, Create Stream -
B.
Database: Usage
Schema: Usage
Table: Select -
C.
Database: Usage, Create Stream
Schema: Usage
Table: Select -
D.
Database: Usage
Schema: Usage, Create Stream
Table: Select
Answer : D
Question 8
What is a characteristic of Snowflake's transaction locking and concurrency modeling?
- A. A deadlock cannot occur in Snowflake, since concurrently executed queries and DML statements do not block one another.
- B. If two queries are concurrently executed against the same table, one of the two queries will be blocked until the other query completes.
- C. Transaction locking in Snowflake is enforced exclusively at the row and table levels.
- D. Queries executed within a given transaction see that transaction's uncommitted changes.
Answer : A
Question 9
A retailer uses a TRANSACTIONS table (100M rows, 1.2 TB) that has been clustered by the STORE_ID column (varchar(50)). The vast majority of analyses on this table are grouped by STORE_ID to look at store performance.
There are 1000 stores operated by the retailer but most sales come from only 20 stores. The Administrator notes that most queries are currently experiencing poor pruning, with large amounts of bytes processed by even simple queries.
Why is this occurring?
- A. The STORE_ID should be numeric.
- B. The table is not big enough to take advantage of the clustering key.
- C. Sales across stores are not uniformly distributed.
- D. The cardinality of the stores to transaction count ratio is too low to use the STORE_ID as a clustering key.
Answer : C
Question 10
A Snowflake Administrator has a multi-cluster virtual warehouse and is using the Snowflake Business-Critical edition. The minimum number of clusters is set to 2 and the maximum number of clusters is set to 10. This configuration works well for the standard workload, rarely exceeding 5 running clusters. However, once a month the Administrator notes that there are a few complex long-running queries that are causing increased queue time and the warehouse reaches its maximum limit at 10 clusters.
Which solutions will address the issues happening once a month? (Choose two.)
- A. Use a task to increase the cluster size for the time period that the more complex queries are running and another task to reduce the size of the cluster once the complex queries complete.
- B. Have the group running the complex monthly queries use a separate appropriately-sized warehouse to support their workload.
- C. Increase the multi-cluster maximum to 20 or more clusters.
- D. Examine the complex queries and determine if they can be made more efficient using clustering keys or materialized views.
- E. Increase the minimum number of clusters started in the multi-cluster configuration to 5.
Answer : BD
Question 11
An organization's sales team leverages this Snowflake query a few times a day:
What can the Snowflake Administrator do to optimize the use of persisted query results whenever possible?
- A. Wrap the query in a User-Defined Function (UDF) to match syntax execution.
- B. Assign everyone on the sales team to the same virtual warehouse.
- C. Assign everyone on the sales team to the same security role.
- D. Leverage the CURRENT_DATE function for date calculations.
Answer : D
Question 12
When does auto-suspend occur for a multi-cluster virtual warehouse?
- A. When there has been no activity on any cluster for the specified period of time.
- B. After a specified period of time when an additional cluster has started on the maximum number of clusters specified for a warehouse.
- C. When the minimum number of clusters is running and there is no activity for the specified period of time.
- D. Auto-suspend does not apply for multi-cluster warehouses.
Answer : C
Question 13
A Snowflake Administrator wants to create a virtual warehouse that supports several dashboards, issuing various queries on the same database.
For this warehouse, why should the Administrator consider setting AUTO_SUSPEND to 0 or NULL?
- A. To save costs on warehouse shutdowns and startups for different queries
- B. To save costs by running the warehouse as little as possible
- C. To keep the data cache warm to support good performance of similar queries
- D. To keep the query result cache warm for good performance on repeated queries
Answer : C
Question 14
If the query matches the definition, will Snowflake always dynamically rewrite the query to use a materialized view?
- A. No, because joins are not supported by materialized views.
- B. No, because the optimizer might decide against it.
- C. No, because the materialized view may not be up-to-date.
- D. Yes, because materialized views are always faster.
Answer : B
Question 15
A Snowflake customer is experiencing higher costs than anticipated while migrating their data warehouse workloads from on-premises to Snowflake. The migration workloads have been deployed on a single warehouse and are characterized by a large number of small INSERTS rather than bulk loading of large extracts. That single warehouse has been configured as a single cluster, 2XL because there are many parallel INSERTs that are scheduled during nightly loads.
How can the Administrator reduce the costs, while minimizing the overall load times, for migrating data warehouse history?
- A. There should be another 2XL warehouse deployed to handle a portion of the load queries.
- B. The 2XL warehouse should be changed to 4XL to increase the number of threads available for parallel load queries.
- C. The warehouse should be kept as a SMALL or XSMALL and configured as a multi-cluster warehouse to handle the parallel load queries.
- D. The INSERTS should be converted to several tables to avoid contention on large tables that slows down query processing.
Answer : C